package com.demodis.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.alibaba.fastjson.JSONObject;
import com.demodis.dao.DeterPersonDaoI;
import com.demodis.domain.TblDetContent;
import com.demodis.domain.TblDeterPerson;
import com.demodis.util.DBUtil;
import com.demodis.util.SystemConstant;

public class DeterPersonDaoImpl extends BaseDao implements DeterPersonDaoI {

	@Override
	public boolean save(TblDeterPerson t) {
		String str1 = t.getPersonSub() == null ? "" : ", personSub";
		String str2 = t.getPersonSup() == null ? "" : ", personSup";
		String str3 = t.getPersonSub() == null ? "" : ", ?";
		String str4 = t.getPersonSup() == null ? "" : ", ?";
		String sql = "INSERT INTO tbl_deterperson(userId, posId, dutyObj, personPic" + str1 + str2 + ") VALUES(?, ?, ?, ?" + str3 + str4 + ");";
		int i = 0;
		try {
			conn = DBUtil.getConnection();
			ps = conn.prepareStatement(sql);

			ps.setInt(1, t.getUserId());
			ps.setInt(2, t.getPosId());
			ps.setString(3, t.getDutyObj());
			ps.setString(4, t.getPersonPic());
			if (t.getPersonSub() != null) {
				ps.setInt(5, t.getPersonSub());
			}
			if (t.getPersonSup() != null) {
				ps.setInt(t.getPersonSub() == null ? 5 : 6, t.getPersonSup());
			}

			i = ps.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, ps, rs);
		}
		return i == 1 ? true : false;
	}

	@Override
	public boolean delete(TblDeterPerson t) {
		String sql = "DELETE FROM A, B, C USING TBL_DETERPERSON A, TBL_CONTRES B, TBL_PERSSUGGEST C WHERE A.USERID = B.USERID AND (C.DETPERID = A.USERID or C.DETERID = A.USERID) AND A.DPID IN(" + t.getIds() + ");";
		int i = 0;
		try {
			conn = DBUtil.getConnection();

			ps = conn.prepareStatement(sql);
			i = ps.executeUpdate();

		} catch (Exception e) {
			e.printStackTrace();
			return false;
		} finally {
			DBUtil.close(conn, ps, rs);
		}
		return i > 0 ? true : false;
	}

	@Override
	public boolean modify(TblDeterPerson t) {
		String str1 = t.getPersonSub() == null ? ", personSub = null" : ", personSub = ?";
		String str2 = t.getPersonSup() == null ? ", personSup = null" : ", personSup = ?";
		String sql = "UPDATE tbl_deterperson SET userId = ?, posId = ? , dutyObj = ?, personPic = ? "+str1 + str2 +" WHERE dpId = ?;";
		int i = 0;
		try {
			conn = DBUtil.getConnection();
			ps = conn.prepareStatement(sql);

			ps.setInt(1, t.getUserId());
			ps.setInt(2, t.getPosId());
			ps.setString(3, t.getDutyObj());
			ps.setString(4, t.getPersonPic());
			//上级和下级都存在
			if(t.getPersonSub() != null && t.getPersonSup() != null) {
				ps.setInt(5, t.getPersonSub());
				ps.setInt(6, t.getPersonSup());
				ps.setInt(7, t.getDpId());
			}
			//下级不存在，上级存在
			if(t.getPersonSub() == null && t.getPersonSup() != null) {
				ps.setInt(5, t.getPersonSup());
				ps.setInt(6, t.getDpId());
			}
			//上级不存在，下级存在
			if(t.getPersonSub() != null && t.getPersonSup() == null) {
				ps.setInt(5, t.getPersonSub());
				ps.setInt(6, t.getDpId());
			}
			//上级和下级都不存在
			if(t.getPersonSub() == null && t.getPersonSup() == null) {
				ps.setInt(5, t.getDpId());
			}

			i = ps.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, ps, rs);
		}

		return i == 1;
	}

	@Override
	public List<TblDeterPerson> get(ResultSet rs) {
		//String sql = "SELECT A.*, B1.USERREALNAME SUPNAME, B2.USERREALNAME SUBNAME FROM (SELECT A.*, B.USERREALNAME, b.groupId, d.groupLeaderId, C.POSNAME, C.POSTYPE, D.GROUPNAME FROM TBL_DETERPERSON A, TBL_USER B ,TBL_POSITION C, TBL_GROUP D WHERE A.USERID = B.USERID AND A.POSID = C.POSID AND B.GROUPID = D.GROUPID) A LEFT JOIN TBL_USER B1 ON A.PERSONSUP = B1.USERID LEFT JOIN TBL_USER B2 ON A.PERSONSUB = B2.USERID WHERE A.posId = ? LIMIT ?, ?;";
		List<TblDeterPerson> list = new ArrayList<TblDeterPerson>();
		try {
			while (rs.next()) {
				TblDeterPerson deterPer = new TblDeterPerson();
				deterPer.setDpId(rs.getInt("dpId"));
				deterPer.setUserId(rs.getInt("userId"));
				deterPer.setUserRealName(rs.getString("userRealName"));
				deterPer.setGroupId(rs.getInt("groupId"));
				deterPer.setGroupLeaderId(rs.getInt("groupLeaderId"));
				deterPer.setDutyObj(rs.getString("dutyObj"));
				deterPer.setPersonPic(rs.getString("personPic"));
				deterPer.setPosId(rs.getInt("posId"));
				deterPer.setPosName(rs.getString("posName"));
				deterPer.setPosType(rs.getString("posType"));
				deterPer.setGroupName(rs.getString("groupName"));
				deterPer.setPersonSup(rs.getInt("personSup"));
				deterPer.setSupName(rs.getString("supName"));
				deterPer.setPersonSub(rs.getInt("personSub"));
				deterPer.setSubName(rs.getString("subName"));
				list.add(deterPer);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, ps, rs);
		}
		return list;
	}

	@Override
	public boolean doDeter(TblDetContent t) {
		return false;
	}

	@Override
	public boolean perExist(int userId) {
		boolean falg = false;
		String sql = "SELECT * FROM TBL_DETERPERSON  WHERE USERID = ?;";
		try {
			conn = DBUtil.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setInt(1, userId);
			rs = ps.executeQuery();
			if(rs.next()) {
				falg = true;
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, ps, rs);
		}
		return falg;
	}

	@Override
	public List<Map<String, Object>> getGroupEmpApp() {
		List<Map<String, Object>> list = new ArrayList<Map<String,Object>>();
		Map<String, Object> map = null;
		
		String sql = "SELECT m.userRealName, n.* FROM tbl_user m, (SELECT a.*, b.id, b.groupAppraise, b.reciprocalAppraise, b.firstGroupAppraise  FROM  tbl_deterperson a LEFT JOIN tbl_groupEmpApp b ON a.userId = b.userId WHERE a.posId = 5) n WHERE m.userId = n.userId;";
		try {
			conn = DBUtil.getConnection();
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while(rs.next()) {
				map = new HashMap<String, Object>();
				map.put("userId", rs.getInt("userId"));
				map.put("userName", rs.getString("userRealName"));
				map.put("id", rs.getInt("id"));
				map.put("groupAppraise", rs.getInt("groupAppraise"));
				map.put("reciprocalAppraise", rs.getInt("reciprocalAppraise"));
				map.put("firstGroupAppraise", rs.getInt("firstGroupAppraise"));
				list.add(map);
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, ps, rs);
		}
		return list;
	}

	@Override
	public boolean addAppScore(Map<String, Integer> map) {
		String sql = "INSERT INTO tbl_groupEmpApp(userId, groupAppraise, reciprocalAppraise, firstGroupAppraise) VALUES(?, ?, ?, ?);";
		int i = 0;
		try {
			conn = DBUtil.getConnection();
			ps = conn.prepareStatement(sql);

			ps.setInt(1, map.get("userId"));
			ps.setInt(2, map.get("groupAppraise"));
			ps.setInt(3, map.get("reciprocalAppraise"));
			ps.setInt(4, map.get("firstGroupAppraise"));

			i = ps.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, ps, rs);
		}
		return i == 1 ? true : false;
	}
	@Override
	public boolean modifyAppScore(Map<String, Integer> map) {
		String sql = "UPDATE tbl_groupEmpApp SET groupAppraise = ?, reciprocalAppraise = ?, firstGroupAppraise = ? WHERE id = ?;";
		int i = 0;
		try {
			conn = DBUtil.getConnection();
			ps = conn.prepareStatement(sql);

			ps.setInt(1, map.get("groupAppraise"));
			ps.setInt(2, map.get("reciprocalAppraise"));
			ps.setInt(3, map.get("firstGroupAppraise"));
			ps.setInt(4, map.get("id"));

			i = ps.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, ps, rs);
		}

		return i == 1;
	}

	@Override
	public List<TblDeterPerson> get() {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public int getTotal(TblDeterPerson deterPer) {
		String sql = "SELECT COUNT(a.posId) FROM tbl_position a, tbl_deterperson b WHERE a.posId = b.posId AND a.posId = ?;";
		int i = 0;
		try {
			conn = DBUtil.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setInt(1, deterPer.getPosId());

			rs = ps.executeQuery();

			if (rs.next()) {
				i = rs.getInt(1);
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, ps, rs);
		}
		return i;
	}

	@Override
	public List<TblDeterPerson> getByGroupId(TblDeterPerson deterPerson) {
		String sql = "SELECT a.userId, a.userRealName, b.groupName, c.posId, d.posName FROM tbl_user a, tbl_group b, tbl_deterperson c, tbl_position d WHERE a.groupId = b.groupId AND a.userId = c.userId AND c.posId = d.posId AND b.groupId = ? LIMIT ?, ?;";
		List<TblDeterPerson> list = new ArrayList<TblDeterPerson>();
		try {
			conn = DBUtil.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setInt(1, deterPerson.getGroupId());
			ps.setInt(2, (deterPerson.getPage() - 1) * deterPerson.getRows());
			ps.setInt(3, deterPerson.getRows());
			rs = ps.executeQuery();
			while (rs.next()) {
				TblDeterPerson deterPer = new TblDeterPerson();
				deterPer.setUserId(rs.getInt("userId"));
				deterPer.setUserRealName(rs.getString("userRealName"));
				deterPer.setGroupName(rs.getString("groupName"));
				deterPer.setPosId(rs.getInt("posId"));
				deterPer.setPosName(rs.getString("posName"));
				deterPer.setGroupId(deterPerson.getGroupId());
				list.add(deterPer);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, ps, rs);
		}
		return list;
	}

	@Override
	public boolean doDeter(JSONObject jsonObj) {
		String sql = "INSERT INTO tbl_deterPersonResult(userId, doDeterUserId, score) VALUES(?, ?, ?);";
		int i = 0;
		try {
			conn = DBUtil.getConnection();
			ps = conn.prepareStatement(sql);

			ps.setInt(1, jsonObj.getIntValue("userId"));
			ps.setInt(2, jsonObj.getIntValue("doDeterUserId"));
			ps.setDouble(3, Double.parseDouble(jsonObj.getString("addupRes")));

			i = ps.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, ps, rs);
		}
		return i == 1 ? true : false;
	}

	@Override
	public int getUserPosIdByUserId(int userId) {
		int posId = 0;
		String sql = "SELECT posId FROM tbl_deterperson WHERE userId = ?;";
		try {
			conn = DBUtil.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setInt(1, userId);
			rs = ps.executeQuery();
			if(rs.next()) {
				posId = rs.getInt("posId");
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, ps, rs);
		}
		return posId;
	}

	@Override
	public List<TblDeterPerson> presidentAndSecretariatCanDeterUser(TblDeterPerson deterPer, int userId) {
		String sql = "SELECT A.*, B1.USERREALNAME SUPNAME, B2.USERREALNAME SUBNAME FROM (SELECT A.*, B.USERREALNAME, b.groupId, d.groupLeaderId, C.POSNAME, C.POSTYPE, D.GROUPNAME FROM TBL_DETERPERSON A, TBL_USER B ,TBL_POSITION C, TBL_GROUP D WHERE A.USERID = B.USERID AND A.POSID = C.POSID AND B.GROUPID = D.GROUPID) A LEFT JOIN TBL_USER B1 ON A.PERSONSUP = B1.USERID LEFT JOIN TBL_USER B2 ON A.PERSONSUB = B2.USERID WHERE A.posId = ? AND A.userId IN(SELECT userId FROM tbl_deterperson WHERE posId IN(?, ?)) LIMIT ?, ?";
		try {
			conn = DBUtil.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setInt(1, deterPer.getPosId());
			ps.setInt(2, SystemConstant.SCHOOLLEADERPOSID);
			ps.setInt(3, SystemConstant.ADMINISTRATIONEMPLOYEEPOSID);
			ps.setInt(4, (deterPer.getPage() - 1) * deterPer.getRows());
			ps.setInt(5, deterPer.getRows());
			rs = ps.executeQuery();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return this.get(rs);
	}

	@Override
	public List<TblDeterPerson> schoolLeaderCanDeterUser(TblDeterPerson deterPer, int userId) {
		String sql = "SELECT A.*, B1.USERREALNAME SUPNAME, B2.USERREALNAME SUBNAME FROM (SELECT A.*, B.USERREALNAME, b.groupId, d.groupLeaderId, C.POSNAME, C.POSTYPE, D.GROUPNAME FROM TBL_DETERPERSON A, TBL_USER B ,TBL_POSITION C, TBL_GROUP D WHERE A.USERID = B.USERID AND A.POSID = C.POSID AND B.GROUPID = D.GROUPID) A LEFT JOIN TBL_USER B1 ON A.PERSONSUP = B1.USERID LEFT JOIN TBL_USER B2 ON A.PERSONSUB = B2.USERID WHERE A.posId = ? AND A.userId IN(SELECT userId FROM tbl_deterperson WHERE posId IN(?, ?)) LIMIT ?, ?";
		try {
			conn = DBUtil.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setInt(1, deterPer.getPosId());
			ps.setInt(2, SystemConstant.ADMINISTRATIONEMPLOYEEPOSID);
			ps.setInt(3, SystemConstant.TWOLEVELLEADERPOSID);
			ps.setInt(4, (deterPer.getPage() - 1) * deterPer.getRows());
			ps.setInt(5, deterPer.getRows());
			rs = ps.executeQuery();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return this.get(rs);
	}

	@Override
	public List<TblDeterPerson> twoLeveLeaderCanDeterUser(TblDeterPerson deterPer, int userId) {
		// 其他中干，所有院领导、所有职能部门员工
		String sql = "SELECT A.*, B1.USERREALNAME SUPNAME, B2.USERREALNAME SUBNAME FROM (SELECT A.*, B.USERREALNAME, b.groupId, d.groupLeaderId, C.POSNAME, C.POSTYPE, D.GROUPNAME FROM TBL_DETERPERSON A, TBL_USER B ,TBL_POSITION C, TBL_GROUP D WHERE A.USERID = B.USERID AND A.POSID = C.POSID AND B.GROUPID = D.GROUPID) A LEFT JOIN TBL_USER B1 ON A.PERSONSUP = B1.USERID LEFT JOIN TBL_USER B2 ON A.PERSONSUB = B2.USERID WHERE A.posId = ? AND A.userId IN(SELECT userId FROM tbl_deterperson WHERE posId IN(?, ?, ?)) AND a.userId != ? LIMIT ?, ?";
		try {
			conn = DBUtil.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setInt(1, deterPer.getPosId());
			ps.setInt(2, SystemConstant.ADMINISTRATIONEMPLOYEEPOSID);
			ps.setInt(3, SystemConstant.TWOLEVELLEADERPOSID);
			ps.setInt(4, SystemConstant.SCHOOLLEADERPOSID);
			ps.setInt(5, userId);
			ps.setInt(6, (deterPer.getPage() - 1) * deterPer.getRows());
			ps.setInt(7, deterPer.getRows());
			rs = ps.executeQuery();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return this.get(rs);
	}

	@Override
	public List<TblDeterPerson> ortherCanDeterUser(TblDeterPerson deterPer, int userId) {
		String sql = "SELECT A.*, B1.USERREALNAME SUPNAME, B2.USERREALNAME SUBNAME FROM (SELECT A.*, B.USERREALNAME, b.groupId, d.groupLeaderId, C.POSNAME, C.POSTYPE, D.GROUPNAME FROM TBL_DETERPERSON A, TBL_USER B ,TBL_POSITION C, TBL_GROUP D WHERE A.USERID = B.USERID AND A.POSID = C.POSID AND B.GROUPID = D.GROUPID) A LEFT JOIN TBL_USER B1 ON A.PERSONSUP = B1.USERID LEFT JOIN TBL_USER B2 ON A.PERSONSUB = B2.USERID WHERE A.posId = ? "
				+ "AND A.userId IN( SELECT userId FROM tbl_deterperson WHERE posId = ? " +
				"OR userId IN(SELECT c.userId FROM tbl_user a, tbl_user b, tbl_deterperson c WHERE a.groupId = b.groupId AND c.userId = b.userId AND c.posId = ? AND a.userId = ?) " +
				"OR userId IN(SELECT b.groupLeaderId FROM tbl_user a, tbl_group b WHERE a.groupId = b.groupId AND a.userId = ?)) LIMIT ?, ?;";
		try {
			conn = DBUtil.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setInt(1, deterPer.getPosId());
			ps.setInt(2, SystemConstant.ADMINISTRATIONEMPLOYEEPOSID);
			ps.setInt(3, SystemConstant.TWOLEVELLEADERPOSID);
			ps.setInt(4, userId);
			ps.setInt(5, userId);
			ps.setInt(6, (deterPer.getPage() - 1) * deterPer.getRows());
			ps.setInt(7, deterPer.getRows());
			rs = ps.executeQuery();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return this.get(rs);
	}

	@Override
	public List<TblDeterPerson> allDeterUser(TblDeterPerson deterPer, int userId) {
		// 其他中干，所有院领导、所有职能部门员工
				String sql = "SELECT A.*, B1.USERREALNAME SUPNAME, B2.USERREALNAME SUBNAME FROM (SELECT A.*, B.USERREALNAME, b.groupId, d.groupLeaderId, C.POSNAME, C.POSTYPE, D.GROUPNAME FROM TBL_DETERPERSON A, TBL_USER B ,TBL_POSITION C, TBL_GROUP D WHERE A.USERID = B.USERID AND A.POSID = C.POSID AND B.GROUPID = D.GROUPID) A LEFT JOIN TBL_USER B1 ON A.PERSONSUP = B1.USERID LEFT JOIN TBL_USER B2 ON A.PERSONSUB = B2.USERID WHERE A.posId = ? LIMIT ?, ?";
				try {
					conn = DBUtil.getConnection();
					ps = conn.prepareStatement(sql);
					ps.setInt(1, deterPer.getPosId());
					ps.setInt(2, (deterPer.getPage() - 1) * deterPer.getRows());
					ps.setInt(3, deterPer.getRows());
					rs = ps.executeQuery();
				} catch (SQLException e) {
					e.printStackTrace();
				}
				return this.get(rs);
	}

}
